﻿Imports ccapps.Web.page
Imports System.Data.OracleClient
Namespace persistent

    Public Class LACustomerDetails
        Private Shared ConnStr As String = GlobalVar.OraConnStr

        Private Shared dbPackageName As String = "EU_BILL.LACustomerDetails."

        Public Shared Function getContractsDetail(
                                           ByVal pin_custcode As String) As DataTable

            Dim command As New OracleCommand(dbPackageName & "getContractsDetail")
            command.Parameters.Add("pin_custcode", OracleType.VarChar).Value = pin_custcode 'Customer ID Bilgisinin Databaseden Alınması 
            command.Parameters.Add("p_cursor", OracleType.Cursor).Direction = ParameterDirection.Output 'Customer ID Bilgisinin Databaseden Alınması 

            Return ExecuteDataTable(command)
        End Function
        Public Shared Function getDealer1(
                                         ByVal pin_custcode As String
                                         ) As DataTable

            Dim command As New OracleCommand(dbPackageName & "getDealer1")
            command.Parameters.Add("pin_custcode", OracleType.VarChar).Value = pin_custcode 'Customer ID Bilgisinin Databaseden Alınması 
            command.Parameters.Add("p_cursor", OracleType.Cursor).Direction = ParameterDirection.Output 'Customer ID Bilgisinin Databaseden Alınması 

            Return ExecuteDataTable(command)
        End Function
        Public Shared Function getDealer2(
                                       ByVal pin_custcode As String) As DataTable

            Dim command As New OracleCommand(dbPackageName & "getDealer2")
            command.Parameters.Add("pin_custcode", OracleType.VarChar).Value = pin_custcode 'Customer ID Bilgisinin Databaseden Alınması 
            command.Parameters.Add("p_cursor", OracleType.Cursor).Direction = ParameterDirection.Output 'Customer ID Bilgisinin Databaseden Alınması 

            Return ExecuteDataTable(command)
        End Function
        'PROCEDURE GetCustomerType (p_custcode IN varchar2,p_prgcode IN VARCHAR2,p_tmcode IN INTEGER,p_tmtype IN VARCHAR2, p_cursor            OUT r_cursor_type) 
        Public Shared Function CustControl(ByVal pin_custcode As String) As DataTable

            Dim command As New OracleCommand(dbPackageName & "GetCustomerType")
            command.Parameters.Add("p_custcode", OracleType.VarChar).Value = pin_custcode 'Customer ID Bilgisinin Databaseden Alınması 
            command.Parameters.Add("p_cursor", OracleType.Cursor).Direction = ParameterDirection.Output 'Customer ID Bilgisinin Databaseden Alınması 

            Return ExecuteDataTable(command)
        End Function
        Public Shared Function getPromotions(
                                     ByVal pin_Custcode As String) As DataTable

            Dim command As New OracleCommand(dbPackageName & "getPromotions")
            command.Parameters.Add("pin_Custcode", OracleType.VarChar).Value = pin_Custcode 'Customer ID Bilgisinin Databaseden Alınması 
            command.Parameters.Add("p_cursor", OracleType.Cursor).Direction = ParameterDirection.Output 'Customer ID Bilgisinin Databaseden Alınması 

            Return ExecuteDataTable(command)
        End Function
        Public Shared Function getFu(
                                   ByVal pin_Custcode As String) As DataTable

            Dim command As New OracleCommand(dbPackageName & "getFu")
            command.Parameters.Add("pin_Custcode", OracleType.VarChar).Value = pin_Custcode 'Customer ID Bilgisinin Databaseden Alınması 
            command.Parameters.Add("p_cursor", OracleType.Cursor).Direction = ParameterDirection.Output 'Customer ID Bilgisinin Databaseden Alınması 

            Return ExecuteDataTable(command)
        End Function
        Public Shared Function getFatura(
                               ByVal pin_custCode As String) As DataTable

            Dim command As New OracleCommand(dbPackageName & "getFatura")
            command.Parameters.Add("pin_custCode", OracleType.VarChar).Value = pin_custCode 'Customer ID Bilgisinin Databaseden Alınması 
            command.Parameters.Add("p_cursor", OracleType.Cursor).Direction = ParameterDirection.Output 'Customer ID Bilgisinin Databaseden Alınması 

            Return ExecuteDataTable(command)
        End Function
        Public Shared Function getPenalty(
                              ByVal pin_custCode As String) As DataTable

            Dim command As New OracleCommand(dbPackageName & "getPenalty")
            command.Parameters.Add("pin_custCode", OracleType.VarChar).Value = pin_custCode 'Customer ID Bilgisinin Databaseden Alınması 
            command.Parameters.Add("p_cursor", OracleType.Cursor).Direction = ParameterDirection.Output 'Customer ID Bilgisinin Databaseden Alınması 

            Return ExecuteDataTable(command)
        End Function

        Public Shared Function getPaymentResponsibleCustCodes(
                             ByVal pin_CustomerID As String) As DataTable

            Dim command As New OracleCommand(dbPackageName & "getPaymentResponsibleCustCodes")
            command.Parameters.Add("pin_CustomerID", OracleType.VarChar).Value = pin_CustomerID 'Customer ID Bilgisinin Databaseden Alınması 
            command.Parameters.Add("p_cursor", OracleType.Cursor).Direction = ParameterDirection.Output 'Customer ID Bilgisinin Databaseden Alınması 

            Return ExecuteDataTable(command)
        End Function
        Public Shared Function getCallSMSBlackberryPacks(ByVal custCode As String) As DataTable

            Dim command As New OracleCommand(dbPackageName & "getCallSMSBlackberryPacks")
            command.Parameters.Add("pincustcode", OracleType.VarChar).Value = custCode 'Customer ID Bilgisinin Databaseden Alınması 
            command.Parameters.Add("p_cursor", OracleType.Cursor).Direction = ParameterDirection.Output 'Customer ID Bilgisinin Databaseden Alınması 

            Return ExecuteDataTable(command)
        End Function
        Public Shared Function getCustomerDetail(
                          ByVal custCode As String) As DataTable

            Dim command As New OracleCommand(dbPackageName & "getCustomerDetail")
            command.Parameters.Add("pin_custcode", OracleType.VarChar).Value = custCode 'Customer ID Bilgisinin Databaseden Alınması 
            command.Parameters.Add("p_cursor", OracleType.Cursor).Direction = ParameterDirection.Output 'Customer ID Bilgisinin Databaseden Alınması 

            Return ExecuteDataTable(command)
        End Function
        Public Shared Function getCustCode(
                       ByVal msisdn As String) As DataTable

            Dim command As New OracleCommand(dbPackageName & "getCustCode")
            command.Parameters.Add("pin_msisdn", OracleType.VarChar).Value = msisdn 'Customer ID Bilgisinin Databaseden Alınması 
            command.Parameters.Add("p_cursor", OracleType.Cursor).Direction = ParameterDirection.Output 'Customer ID Bilgisinin Databaseden Alınması 

            Return ExecuteDataTable(command)
        End Function
        Public Shared Function GetCustInfoFromCustcode(ByVal pin_custcode As String) As DataTable

            Dim command As New OracleCommand(dbPackageName & "GetCustInfoFromCustcode")
            command.Parameters.Add("pin_custcode", OracleType.VarChar).Value = pin_custcode
            command.Parameters.Add("p_cursor", OracleType.Cursor).Direction = ParameterDirection.Output

            ''LOGLAMA
            Dim Query As String = "Select * from dual "
            Dim gen As Object = New CustomerDetails()
            persistent.myDBConn.ORAScalar_LOG(Query, pin_custcode, "null", "null", "null", "Select(GetCustInfoFromCustcode)", "CustomerDetails", gen.Session("user"))

            Return ExecuteDataTable(command)



        End Function
        Public Shared Function GetCustomerContactDetails(ByVal pin_customer_id As Integer) As DataTable

            Dim command As New OracleCommand(dbPackageName & "GetCustomerContactDetails")
            command.Parameters.Add("pin_customer_id", OracleType.VarChar).Value = pin_customer_id
            command.Parameters.Add("p_cursor", OracleType.Cursor).Direction = ParameterDirection.Output
            ''LOGLAMA
            Dim Query As String = "Select * from dual "
            Dim gen As Object = New CustomerDetails()
            persistent.myDBConn.ORAScalar_LOG(Query, "null", pin_customer_id, "null", "null", "Select(GetCustomerContactDetails)", "CustomerDetails", gen.Session("user"))

            Return ExecuteDataTable(command)


        End Function
        Public Shared Function GetCustInfoFromMSISDN(ByVal pin_MSISDN As String) As DataTable
            Dim pin_MSISDN2 As String = "90" + pin_MSISDN
            Dim command As New OracleCommand(dbPackageName & "GetCustInfoFromMSISDN")
            command.Parameters.Add("pin_MSISDN", OracleType.VarChar).Value = pin_MSISDN2
            command.Parameters.Add("p_cursor", OracleType.Cursor).Direction = ParameterDirection.Output
            ''LOGLAMA
            Dim Query As String = "Select * from dual "
            Dim gen As Object = New CustomerDetails()
            persistent.myDBConn.ORAScalar_LOG(Query, "null", "null", "null", pin_MSISDN, "Select(GetCustInfoFromMSISDN)", "CustomerDetails", gen.Session("user"))

            Return ExecuteDataTable(command)



        End Function


        Public Shared Function ExecuteNonQuery(ByVal command As OracleCommand, ByRef MESSAGE As String)
            Dim connection As New OracleConnection(ConnStr)
            command.Connection = connection
            command.CommandType = CommandType.StoredProcedure

            Try
                connection.Open()
                command.ExecuteNonQuery()
            Catch ex As Exception
                MESSAGE = ex.Message.ToString
            Finally
                connection.Close()
            End Try
        End Function
        Public Shared Function ExecuteDataTable(ByVal command As OracleCommand, Optional ByVal directSql As Boolean = 0) As DataTable
            Dim connection As New OracleConnection(ConnStr)
            Dim dataAdapter As New OracleDataAdapter(command)
            Dim dataTable As New DataTable
            Dim dataSet As New DataSet

            command.Connection = connection

            If Not (directSql) Then
                command.CommandType = CommandType.StoredProcedure
            End If

            Try
                connection.Open()
                dataAdapter.Fill(dataSet)
                dataTable = dataSet.Tables(0)
            Catch ex As Exception
            Finally
                connection.Close()
                dataSet.Dispose()
            End Try

            Return dataTable
        End Function
    End Class
End Namespace


